package com.ys.controller.api.finance;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ys.config.MenuId;
import com.ys.config.MyConfig;
import com.ys.entity.base.TbUserMonthPerformance;
import com.ys.entity.finance.TcwFinanceSalaryTotal;
import com.ys.entity.sys.YsCol;
import com.ys.service.finance.TcwFinanceSalaryTotalService;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.UploadService;
import com.ys.util.CheckUtil;
import com.ys.util.admin.ConditionUtil;
import com.ys.vo.LayuiTableVoJA;
import com.ys.vo.R;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 财务工资小计汇总表
 */
@Controller
@RequestMapping("/api/inter/tcwFinanceSalaryTotal")
public class ApiInterTcwFinanceSalaryTotalController {
    private static final Logger logger = LoggerFactory.getLogger(ApiInterTcwFinanceSalaryTotalController.class);

    @Autowired
    private CommonApiService<TcwFinanceSalaryTotal> commonApiService;

    //@Autowired
    //private CommonApiService<TbUserPaySlip> commonTbUserPaySlipApiService;

    @Autowired
    private CommonApiService<TbUserMonthPerformance> commonTbUserMonthPerformanceApiService;

    @Autowired
    private TcwFinanceSalaryTotalService tcwFinanceSalaryTotalService;

    @Autowired
    private CheckUtil checkUtil;

    /**
     * 根据所选择的月份，更新汇总数据
     */
    @RequestMapping("/updateTotalByCountMonth")
    @ResponseBody
    public R updateTotalByCountMonth(HttpServletRequest request, @RequestParam Map<String, Object> params){
        try {
            String countMonth = params.get("countMonth").toString();
            return tcwFinanceSalaryTotalService.updateTotalByCountMonth(countMonth);
        } catch (Exception e) {
            logger.error("出错：",e.getMessage(),e);
            return R.error("系统正在开小差，请联系客服，我们将第一时间为您解决！");
        }
    }

    /**
     * 设置查询的cond的sql设置
     * searchType : 0工资单、1财务社保表、2财务医保表、3财务公积金表
     */
    public TbUserMonthPerformance setSeachSqlByPaySlipDetail(TbUserMonthPerformance cond,int searchType){
        cond.addLeftJoin("TS_UserInfo U ON U.UserId = A.UserId")//用户信息表
                //.addField("(U.UserId + '/' + U.UserName) AS _ys_nameLinked")//工号/姓名
                .addField("U.UserName AS _ys_nameLinked")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo OI ON OI.Orgid = U.DeptId")
                .addField("CASE WHEN OI.Orgid = 'D250' THEN '研发部' WHEN OI.Orgid = 'C101' THEN '总裁办' ELSE OI.OrgName END deptName")//部门名称
                .addField("'0' AS personalTaxAdjust")//个人所得税调整补发
                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = A.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司

                //排序
                .setOrderby("A.PointMonth DESC,U.DeptId ASC");

        if(searchType==0){//工资单
            cond.addLeftJoin("TB_UserPaySlip S ON A.Id = S.MonthPerformanceId")//工资条
                    .addField("S.WagesPay AS wagesPay")//应发工资
                    .addField("S.OtherCost AS otherCost")//其他费用
                    .addField("S.WagesPay + S.OtherCost  AS wagesAmount")//工资
                    .addField("S.HealthCompany AS healthCompany")//医保缴纳金额-企业
                    .addField("S.HealthPersonal AS healthPersonal")//医保缴纳金额-个人
                    .addField("S.RetirementCompany AS retirementCompany")//养老缴纳金额-企业
                    .addField("S.RetirementPersonal AS retirementPersonal")//养老缴纳金额-个人
                    .addField("S.InjuryCompany AS injuryCompany")//工伤缴纳金额-企业
                    .addField("S.LoseWorkCompany AS loseWorkCompany")//失业缴纳金额-企业
                    .addField("S.LoseWorkPersonal AS loseWorkPersonal")//失业缴纳金额-个人
                    .addField("S.BirthCompany AS birthCompany")//生育
                    .addField("S.PersonalTax AS personalTax")//个税
                    .addField("S.AccumulationFundCompany AS accumulationFundCompany")//公积金-企业
                    .addField("S.AccumulationFundPersonal AS accumulationFundPersonal")//公积金-个人
                    .addField("S.RealityPaySlip AS realityPaySlip")//实发工资
                    .where("S.SlipStatus = 3")//已发放
            ;
        }else if(searchType==1){//财务社保表
            cond.addLeftJoin("TB_SocialSecurityPayItem S ON S.UserMonthPerformanceId = A.Id")//社保付款子表
                    .addLeftJoin("TB_SocialSecurityPay P ON S.SocialSecurityPayBid = P.Bid")//社保付款单主表
                    .addField("S.RetirementCompany AS retirementCompany")//养老缴纳金额-企业
                    .addField("S.RetirementPersonal AS retirementPersonal")//养老缴纳金额-个人
                    .addField("S.InjuryCompany AS injuryCompany")//工伤缴纳金额-企业
                    .addField("S.LoseWorkCompany AS loseWorkCompany")//失业缴纳金额-企业
                    .addField("S.LoseWorkPersonal AS loseWorkPersonal")//失业缴纳金额-个人
                    .where("S.UserMonthPerformanceId IS NOT NULL")
                    //.where("P.Status = 1")//
                    //.where("P.EffectStock = 1")//已生效
            ;
        }else if(searchType==2){//财务医保表
            cond.addLeftJoin("TB_MedicarePayItem S ON S.UserMonthPerformanceId = A.Id")//医保付款单子表
                    .addLeftJoin("TB_MedicarePay P ON S.MedicarePayBid = P.Bid")//医保付款单主表
                    .addField("S.HealthCompany AS healthCompany")//医保缴纳金额-企业
                    .addField("S.HealthPersonal AS healthPersonal")//医保缴纳金额-个人
                    .addField("S.BirthCompany AS birthCompany")//生育
                    .where("S.UserMonthPerformanceId IS NOT NULL")
                    //.where("P.Status = 1")//
                    //.where("P.EffectStock = 1")//已生效
            ;
        }else if(searchType==3){//财务公积金表
            cond.addLeftJoin("TB_ReservedPayItem S ON S.UserMonthPerformanceId = A.Id")//公积金付款单子表
                    .addLeftJoin("TB_ReservedPay P ON S.ReservedPayBid = P.Bid")//公积金付款单主表
                    .addField("S.AccumulationFundCompany AS accumulationFundCompany")//公积金-企业
                    .addField("S.AccumulationFundPersonal AS accumulationFundPersonal")//公积金-个人
                    .where("S.UserMonthPerformanceId IS NOT NULL")
                    //.where("P.Status = 1")//
                    //.where("P.EffectStock = 1")//已生效
            ;
        }
        return  cond;
    }

    /**
     * 设置查询的cond的sql设置
     */
    public TcwFinanceSalaryTotal setSeachSqlByPaySlipTotal(TcwFinanceSalaryTotal cond){
        cond
                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = A.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                //.addField("O.OrgPath AS orgPath")//路径
                //排序
                .setOrderby("A.CountMonth DESC,A.OrgId ASC,A.Id ASC");
        return  cond;
    }


    //导出
    public R exportByTotal(List<YsCol> listField,String fileName,List<Map<String, Object>> listData,String countMonth,int exportType) throws Exception{
        //获取所有的公司信息
        List<Map<String, Object>> orgInfoList = new ArrayList<>();
        if(exportType==1){
            orgInfoList = tcwFinanceSalaryTotalService.getOrgNameList(countMonth);
        }else{
            orgInfoList = tcwFinanceSalaryTotalService.getOrgNameByUserPaySlip(countMonth);
        }
        //获取对应公司的统计信息
        List<Map<String, Object>> orgIdTotalList = new ArrayList<>();
        if(exportType==1){
            orgIdTotalList = tcwFinanceSalaryTotalService.countFinanceSalaryTotalByOrgId(countMonth);
        }else {
            orgIdTotalList = tcwFinanceSalaryTotalService.countUserPaySlipByOrgId(countMonth);
        }

        //遍历公司信息
        if(orgInfoList!=null && orgInfoList.size()>0 && orgIdTotalList!=null && orgIdTotalList.size()>0){
            //创建一个工作簿
            SXSSFWorkbook wb = new SXSSFWorkbook(100);
            Map<String, CellStyle> styles = createStyles(wb);

            CellStyle cellStyle = wb.createCellStyle();		//预设样式
            cellStyle.cloneStyleFrom(styles.get("header"));	//预设样式

            for(Map<String, Object> orgItem:orgInfoList){
                String orgId = orgItem.get("orgid").toString();
                String orgName = orgItem.get("orgName").toString();
                //String orgPath = orgItem.get("orgPath").toString();
                String[] countMonthArr = countMonth.split("-");
                String titleMsg = orgName + countMonthArr[0] + "年" + countMonthArr[1] + "月" + fileName;

                //创建Sheet
                Sheet sheet = wb.createSheet(orgName);




                //获取表头信息
                Map<String,List<String>> headMsg = setHeadMsg(titleMsg,listField);
                List<String> headerMsg1 = headMsg.get("headerMsg1");//一级表头
                List<String> headerMsg2 = headMsg.get("headerMsg2");//二级表头
                // 创建表头行
                Row headerRow = sheet.createRow(0);
                for(int i=0;i<headerMsg1.size();i++){
                    // 创建表头单元格
                    Cell headerCell = headerRow.createCell(i);
                    headerCell.setCellValue(headerMsg1.get(i));
                    headerCell.setCellStyle(styles.get("header"));
                }
                // 创建表头行
                Row headerRow2 = sheet.createRow(1);
                for(int i=0;i<headerMsg2.size();i++){
                    // 创建表头单元格
                    Cell headerCell = headerRow2.createCell(i);
                    headerCell.setCellValue(headerMsg2.get(i));
                    headerCell.setCellStyle(styles.get("header2"));
                    //sheet.autoSizeColumn(i);
                    // 设置列宽
                    if(headerMsg2.get(i).length()>5){
                        sheet.setColumnWidth(i, 800*headerMsg2.get(i).length());
                    }else {
                        sheet.setColumnWidth(i, 3000);
                    }

                }
                //合并一级表头
                //合并行
                CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, listField.size()-1);
                sheet.addMergedRegion(region0);

                List<Map<String, Object>> listDataByOrg = listData.stream().filter(item->item.get("orgId").toString().equals(orgId)).collect(Collectors.toList());
                List<Map<String, Object>> orgIdTotalListByOrg = orgIdTotalList.stream().filter(item->item.get("orgId").toString().equals(orgId)).collect(Collectors.toList());

                if(orgIdTotalListByOrg.size()>0){
                    listDataByOrg.add(orgIdTotalListByOrg.get(0));
                }
                // 填充数据
                for (int i = 0; i < listDataByOrg.size(); i++) {
                    Map msg = listDataByOrg.get(i);
                    Row dataRow = sheet.createRow(i + 2);
                    for(int j = 0; j < listField.size(); j++) {
                        Cell dataCell = dataRow.createCell(j);
                        String cellMsg = listField.get(j).getCode();
                        if(msg.get(cellMsg)!=null){
                            dataCell.setCellValue(msg.get(cellMsg).toString());
                            dataCell.setCellStyle(styles.get("data"));
                        }
                    }
                }


                // 在表格之外添加信息
                Row dataRow = sheet.createRow(listDataByOrg.size() + 6);
                Cell dataCell1 = dataRow.createCell(headerMsg2.size()/2-1);
                dataCell1.setCellValue("审核：");
                dataCell1.setCellStyle(styles.get("otherdata"));

                Cell dataCell2 = dataRow.createCell(headerMsg2.size()/2-1+3);
                dataCell2.setCellValue("制表：");
                dataCell2.setCellStyle(styles.get("otherdata"));
            }

            // 导出Excel文件
            String filePath = fileName+"_"+new Date().getTime()+".xlsx";
            String downloadPath = MyConfig.getProfile() + "/" + filePath;
            File file = new File(downloadPath);
            FileOutputStream fos = new FileOutputStream(file);
            wb.write(fos);
            fos.close();
            UploadService.UploadDto dto = commonApiService.uploadLocalToServer(downloadPath);
            //返回文件路径
            return R.ok(dto.getUrl());
        }else {
            if(exportType==1){
                int count = new TcwFinanceSalaryTotal().setCountMonth(countMonth).count();
                if(count<=0){
                    return R.error("请先点击\"统计生成\"按钮，生成要导出月份对应的数据！");
                }
            }
            return R.error("公司信息为空！");
        }
        //return R.ok();
    }




    /**
     * 查询 --- 财务工资表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalByPaySlipDetail:view")
    @RequestMapping("/searchByPaySlipDetail")
    @ResponseBody
    public LayuiTableVoJA searchByPaySlipDetail(Model model, HttpServletRequest request, HttpServletResponse response,
                                 @ModelAttribute TbUserMonthPerformance cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipDetail(cond,0);
            cond = commonTbUserMonthPerformanceApiService.dataAuth(MenuId.TCW_FinanceSalaryTotal_ByPaySlipDetail, cond);
            LayuiTableVoJA tableVoJA = commonTbUserMonthPerformanceApiService.searchLay(cond);
            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                //cond.setMainTable("TB_UserPaySlip");
                // 应发工资
                JO.put("wagesPay", cond.setFields("ISNULL(SUM(S.WagesPay),0)").sumDecimal());
                // 其他费用
                JO.put("otherCost", cond.setFields("ISNULL(SUM(S.OtherCost),0)").sumDecimal());
                // 工资
                JO.put("wagesAmount", cond.setFields("ISNULL(SUM(S.WagesPay + S.OtherCost),0)").sumDecimal());
                // 医保缴纳金额-企业
                JO.put("healthCompany", cond.setFields("ISNULL(SUM(S.HealthCompany),0)").sumDecimal());
                // 医保缴纳金额-个人
                JO.put("healthPersonal", cond.setFields("ISNULL(SUM(S.HealthPersonal),0)").sumDecimal());
                // 养老缴纳金额-企业
                JO.put("retirementCompany", cond.setFields("ISNULL(SUM(S.RetirementCompany),0)").sumDecimal());
                // 养老缴纳金额-个人
                JO.put("retirementPersonal", cond.setFields("ISNULL(SUM(S.RetirementPersonal),0)").sumDecimal());
                // 工伤缴纳金额-企业
                JO.put("injuryCompany", cond.setFields("ISNULL(SUM(S.InjuryCompany),0)").sumDecimal());
                // 失业缴纳金额-企业
                JO.put("loseWorkCompany", cond.setFields("ISNULL(SUM(S.LoseWorkCompany),0)").sumDecimal());
                // 失业缴纳金额-个人
                JO.put("loseWorkPersonal", cond.setFields("ISNULL(SUM(S.LoseWorkPersonal),0)").sumDecimal());
                // 生育
                JO.put("birthCompany", cond.setFields("ISNULL(SUM(S.BirthCompany),0)").sumDecimal());
                // 个税
                JO.put("personalTax", cond.setFields("ISNULL(SUM(S.PersonalTax),0)").sumDecimal());
                // 个税差额调整补发
                JO.put("personalTaxAdjust", "0");
                // 公积金-企业
                JO.put("accumulationFundCompany", cond.setFields("ISNULL(SUM(S.AccumulationFundCompany),0)").sumDecimal());
                // 公积金-个人
                JO.put("accumulationFundPersonal", cond.setFields("ISNULL(SUM(S.AccumulationFundPersonal),0)").sumDecimal());
                // 实发工资
                JO.put("realityPaySlip", cond.setFields("ISNULL(SUM(S.RealityPaySlip),0)").sumDecimal());

                tableVoJA.setTotalRow(JO);
            }

            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportByPaySlipDetail")
    @ResponseBody
    public R exportByPaySlipDetail(@ModelAttribute TbUserMonthPerformance cond,String conditions){
        try {
            String pointMonth = ConditionUtil.getConditionValue(conditions, "pointMonth");//统计月份
            if(pointMonth==null || "".equals(pointMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipDetail(cond,0);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('pointMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotal_ByPaySlipDetail)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();

            //处理数据
            return exportByTotal(listField,"员工工资表",listData,pointMonth,0);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }



    /**
     * 查询 --- 财务工资表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalByPaySlipTotal:view")
    @RequestMapping("/searchByPaySlipTotal")
    @ResponseBody
    public LayuiTableVoJA searchByPaySlipTotal(Model model, HttpServletRequest request, HttpServletResponse response,
                                                @ModelAttribute TcwFinanceSalaryTotal cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipTotal(cond);
            cond.where("A.WagesPay > 0 AND A.PersonalTax > 0 AND A.RealityPaySlip > 0 ");
            cond = commonApiService.dataAuth(MenuId.TCW_FinanceSalaryTotal_ByPaySlipTotal, cond);
            LayuiTableVoJA tableVoJA = commonApiService.searchLay(cond);
            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                cond.setMainTable("TCW_FinanceSalaryTotal");
                // 应发工资
                JO.put("wagesPay", cond.setFields("ISNULL(SUM(A.WagesPay),0)").sumDecimal());
                // 其他费用
                JO.put("otherCost", cond.setFields("ISNULL(SUM(A.OtherCost),0)").sumDecimal());
                // 工资
                JO.put("wagesAmount", cond.setFields("ISNULL(SUM(A.WagesAmount),0)").sumDecimal());
                // 养老缴纳金额-企业
                JO.put("retirementCompany", cond.setFields("ISNULL(SUM(A.RetirementCompany),0)").sumDecimal());
                // 养老缴纳金额-个人
                JO.put("retirementPersonal", cond.setFields("ISNULL(SUM(A.RetirementPersonal),0)").sumDecimal());
                // 工伤缴纳金额-企业
                JO.put("injuryCompany", cond.setFields("ISNULL(SUM(A.InjuryCompany),0)").sumDecimal());
                // 失业缴纳金额-企业
                JO.put("loseWorkCompany", cond.setFields("ISNULL(SUM(A.LoseWorkCompany),0)").sumDecimal());
                // 失业缴纳金额-个人
                JO.put("loseWorkPersonal", cond.setFields("ISNULL(SUM(A.LoseWorkPersonal),0)").sumDecimal());
                // 医保缴纳金额-企业
                JO.put("healthCompany", cond.setFields("ISNULL(SUM(A.HealthCompany),0)").sumDecimal());
                // 医保缴纳金额-个人
                JO.put("healthPersonal", cond.setFields("ISNULL(SUM(A.HealthPersonal),0)").sumDecimal());
                // 生育
                JO.put("birthCompany", cond.setFields("ISNULL(SUM(A.BirthCompany),0)").sumDecimal());
                // 个税
                JO.put("personalTax", cond.setFields("ISNULL(SUM(A.PersonalTax),0)").sumDecimal());
                // 个税差额调整补发
                JO.put("personalTaxAdjust", cond.setFields("ISNULL(SUM(A.PersonalTaxAdjust),0)").sumDecimal());
                // 公积金-企业
                JO.put("accumulationFundCompany", cond.setFields("ISNULL(SUM(A.AccumulationFundCompany),0)").sumDecimal());
                // 公积金-个人
                JO.put("accumulationFundPersonal", cond.setFields("ISNULL(SUM(A.AccumulationFundPersonal),0)").sumDecimal());
                // 实发工资
                JO.put("realityPaySlip", cond.setFields("ISNULL(SUM(A.RealityPaySlip),0)").sumDecimal());

                tableVoJA.setTotalRow(JO);
            }
            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportByPaySlipTotal")
    @ResponseBody
    public R exportByPaySlipTotal(@ModelAttribute TcwFinanceSalaryTotal cond,String conditions){
        try {
            String countMonth = ConditionUtil.getConditionValue(conditions, "countMonth");//统计月份
            if(countMonth==null || "".equals(countMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipTotal(cond);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('countMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotal_ByPaySlipTotal)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();


            //处理数据
            return exportByTotal(listField,"员工工资小计",listData,countMonth,1);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }



    //*****************************************************财务社保表*****************************************************
    //处理财务社保表返回的数据
    public List setListBySoseDetail(List<Map<String, Object>> resDate){
        List<Map<String, Object>> tableDate = new ArrayList();
        //计算社保的合计
        resDate.forEach(item->{
            BigDecimal retirementCompany = new BigDecimal(item.get("retirementCompany").toString());
            BigDecimal retirementPersonal = new BigDecimal(item.get("retirementPersonal").toString());
            BigDecimal injuryCompany = new BigDecimal(item.get("injuryCompany").toString());
            BigDecimal loseWorkCompany = new BigDecimal(item.get("loseWorkCompany").toString());
            BigDecimal loseWorkPersonal = new BigDecimal(item.get("loseWorkPersonal").toString());
            BigDecimal soseTotal = retirementCompany.add(retirementPersonal).add(injuryCompany).add(loseWorkCompany).add(loseWorkPersonal);
            item.put("soseTotal",soseTotal);
            tableDate.add(item);
        });
        return tableDate;
    }

    /**
     * 查询 --- 财务社保表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalBySoseDetail:view")
    @RequestMapping("/searchBySoseDetail")
    @ResponseBody
    public LayuiTableVoJA searchBySoseDetail(Model model, HttpServletRequest request, HttpServletResponse response,
                                                @ModelAttribute TbUserMonthPerformance cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipDetail(cond,1);
            cond = commonTbUserMonthPerformanceApiService.dataAuth(MenuId.TCW_FinanceSalaryTotalBySoseDetail, cond);
            LayuiTableVoJA tableVoJA = commonTbUserMonthPerformanceApiService.searchLay(cond);

            //获取
            JSONArray tableVoJAData = tableVoJA.getData();

            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));

            //处理数据
            resList = setListBySoseDetail(resListNew);
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resList));
            tableVoJA.setData(jsonArray);

            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                //cond.setMainTable("TB_UserPaySlip");

                // 养老缴纳金额-企业
                JO.put("retirementCompany", cond.setFields("ISNULL(SUM(S.RetirementCompany),0)").sumDecimal());
                // 养老缴纳金额-个人
                JO.put("retirementPersonal", cond.setFields("ISNULL(SUM(S.RetirementPersonal),0)").sumDecimal());
                // 工伤缴纳金额-企业
                JO.put("injuryCompany", cond.setFields("ISNULL(SUM(S.InjuryCompany),0)").sumDecimal());
                // 失业缴纳金额-企业
                JO.put("loseWorkCompany", cond.setFields("ISNULL(SUM(S.LoseWorkCompany),0)").sumDecimal());
                // 失业缴纳金额-个人
                JO.put("loseWorkPersonal", cond.setFields("ISNULL(SUM(S.LoseWorkPersonal),0)").sumDecimal());
                // 合计
                JO.put("soseTotal", cond.setFields("ISNULL(SUM(S.RetirementCompany + S.RetirementPersonal + S.InjuryCompany + S.LoseWorkCompany + S.LoseWorkPersonal),0)").sumDecimal());

                tableVoJA.setTotalRow(JO);
            }

            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportBySoseDetail")
    @ResponseBody
    public R exportBySoseDetail(@ModelAttribute TbUserMonthPerformance cond,String conditions){
        try {
            String pointMonth = ConditionUtil.getConditionValue(conditions, "pointMonth");//统计月份
            if(pointMonth==null || "".equals(pointMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipDetail(cond,1);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('pointMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotalBySoseDetail)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();
            //处理数据
            listData = setListBySoseDetail(listData);

            //处理数据
            return exportByTotal(listField,"社保扣款明细",listData,pointMonth,0);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }


    /**
     * 查询 --- 财务社保表-小计
     */
    @RequiresPermissions("tcwFinanceSalaryTotalBySoseTotal:view")
    @RequestMapping("/searchBySoseTotal")
    @ResponseBody
    public LayuiTableVoJA searchBySoseTotal(Model model, HttpServletRequest request, HttpServletResponse response,
                                               @ModelAttribute TcwFinanceSalaryTotal cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipTotal(cond);
            cond.where("A.SoseTotal > 0 ");
            cond = commonApiService.dataAuth(MenuId.TCW_FinanceSalaryTotalBySoseTotal, cond);
            LayuiTableVoJA tableVoJA = commonApiService.searchLay(cond);
            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                cond.setMainTable("TCW_FinanceSalaryTotal");
                // 养老缴纳金额-企业
                JO.put("retirementCompany", cond.setFields("ISNULL(SUM(A.RetirementCompany),0)").sumDecimal());
                // 养老缴纳金额-个人
                JO.put("retirementPersonal", cond.setFields("ISNULL(SUM(A.RetirementPersonal),0)").sumDecimal());
                // 工伤缴纳金额-企业
                JO.put("injuryCompany", cond.setFields("ISNULL(SUM(A.InjuryCompany),0)").sumDecimal());
                // 失业缴纳金额-企业
                JO.put("loseWorkCompany", cond.setFields("ISNULL(SUM(A.LoseWorkCompany),0)").sumDecimal());
                // 失业缴纳金额-个人
                JO.put("loseWorkPersonal", cond.setFields("ISNULL(SUM(A.LoseWorkPersonal),0)").sumDecimal());
                // 社保合计
                JO.put("soseTotal", cond.setFields("ISNULL(SUM(A.SoseTotal),0)").sumDecimal());
                tableVoJA.setTotalRow(JO);
            }
            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportBySoseTotal")
    @ResponseBody
    public R exportBySoseTotal(@ModelAttribute TcwFinanceSalaryTotal cond,String conditions){
        try {
            String countMonth = ConditionUtil.getConditionValue(conditions, "countMonth");//统计月份
            if(countMonth==null || "".equals(countMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipTotal(cond);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('countMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotalBySoseTotal)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();

            //处理数据
            return exportByTotal(listField,"社保小计",listData,countMonth,1);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }


    //*****************************************************财务医保表*****************************************************
    //处理财务社保表返回的数据
    public List setListByMiDetail(List<Map<String, Object>> resDate){
        List<Map<String, Object>> tableDate = new ArrayList();
        //计算社保的合计
        resDate.forEach(item->{
            BigDecimal healthCompany = new BigDecimal(item.get("healthCompany").toString());
            BigDecimal healthPersonal = new BigDecimal(item.get("healthPersonal").toString());
            BigDecimal birthCompany = new BigDecimal(item.get("birthCompany").toString());

            BigDecimal miTotal = healthCompany.add(healthPersonal).add(birthCompany);
            item.put("miTotal",miTotal);
            tableDate.add(item);
        });
        return tableDate;
    }

    /**
     * 查询 --- 财务医保表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalByMiDetail:view")
    @RequestMapping("/searchByMiDetail")
    @ResponseBody
    public LayuiTableVoJA searchByMiDetail(Model model, HttpServletRequest request, HttpServletResponse response,
                                             @ModelAttribute TbUserMonthPerformance cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipDetail(cond,2);
            cond = commonTbUserMonthPerformanceApiService.dataAuth(MenuId.TCW_FinanceSalaryTotalByMiDetail, cond);
            LayuiTableVoJA tableVoJA = commonTbUserMonthPerformanceApiService.searchLay(cond);

            //获取
            JSONArray tableVoJAData = tableVoJA.getData();

            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));

            //处理数据
            resList = setListByMiDetail(resListNew);
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resList));
            tableVoJA.setData(jsonArray);

            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                //cond.setMainTable("TB_UserPaySlip");

                // 医保缴纳金额-企业
                JO.put("healthCompany", cond.setFields("ISNULL(SUM(S.HealthCompany),0)").sumDecimal());
                // 医保缴纳金额-个人
                JO.put("healthPersonal", cond.setFields("ISNULL(SUM(S.HealthPersonal),0)").sumDecimal());
                // 生育
                JO.put("birthCompany", cond.setFields("ISNULL(SUM(S.BirthCompany),0)").sumDecimal());
                // 合计
                JO.put("miTotal", cond.setFields("ISNULL(SUM(S.HealthCompany + S.HealthPersonal + S.BirthCompany),0)").sumDecimal());

                tableVoJA.setTotalRow(JO);
            }

            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportByMiDetail")
    @ResponseBody
    public R exportByMiDetail(@ModelAttribute TbUserMonthPerformance cond,String conditions){
        try {
            String pointMonth = ConditionUtil.getConditionValue(conditions, "pointMonth");//统计月份
            if(pointMonth==null || "".equals(pointMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipDetail(cond,2);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('pointMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotalByMiDetail)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();
            //处理数据
            listData = setListByMiDetail(listData);

            //处理数据
            return exportByTotal(listField,"医保扣款明细",listData,pointMonth,0);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }


    /**
     * 查询 --- 财务医保表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalByMiTotal:view")
    @RequestMapping("/searchByMiTotal")
    @ResponseBody
    public LayuiTableVoJA searchByMiTotal(Model model, HttpServletRequest request, HttpServletResponse response,
                                            @ModelAttribute TcwFinanceSalaryTotal cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipTotal(cond);
            cond.where("A.MiTotal > 0 ");
            cond = commonApiService.dataAuth(MenuId.TCW_FinanceSalaryTotalByMiTotal, cond);
            LayuiTableVoJA tableVoJA = commonApiService.searchLay(cond);
            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                cond.setMainTable("TCW_FinanceSalaryTotal");
                // 医保缴纳金额-企业
                JO.put("healthCompany", cond.setFields("ISNULL(SUM(A.HealthCompany),0)").sumDecimal());
                // 医保缴纳金额-个人
                JO.put("healthPersonal", cond.setFields("ISNULL(SUM(A.HealthPersonal),0)").sumDecimal());
                // 生育
                JO.put("birthCompany", cond.setFields("ISNULL(SUM(A.BirthCompany),0)").sumDecimal());
                // 医保合计
                JO.put("miTotal", cond.setFields("ISNULL(SUM(A.MiTotal),0)").sumDecimal());
                tableVoJA.setTotalRow(JO);
            }
            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportByMiTotal")
    @ResponseBody
    public R exportByMiTotal(@ModelAttribute TcwFinanceSalaryTotal cond,String conditions){
        try {
            String countMonth = ConditionUtil.getConditionValue(conditions, "countMonth");//统计月份
            if(countMonth==null || "".equals(countMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipTotal(cond);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('countMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotalByMiTotal)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();

            //处理数据
            return exportByTotal(listField,"医保小计",listData,countMonth,1);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }



    //*****************************************************财务公积金表*****************************************************
    //处理财务社保表返回的数据
    public List setListByHousingDetail(List<Map<String, Object>> resDate){
        List<Map<String, Object>> tableDate = new ArrayList();
        //计算社保的合计
        resDate.forEach(item->{
            BigDecimal accumulationFundCompany = item.get("accumulationFundCompany")!=null?new BigDecimal(item.get("accumulationFundCompany").toString()):BigDecimal.ZERO;
            BigDecimal accumulationFundPersonal = item.get("accumulationFundPersonal")!=null?new BigDecimal(item.get("accumulationFundPersonal").toString()):BigDecimal.ZERO;

            BigDecimal accumulationFundTotal = accumulationFundCompany.add(accumulationFundPersonal);
            item.put("accumulationFundTotal",accumulationFundTotal);
            tableDate.add(item);
        });
        return tableDate;
    }

    /**
     * 查询 --- 财务医保表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalByHousingDetail:view")
    @RequestMapping("/searchByHousingDetail")
    @ResponseBody
    public LayuiTableVoJA searchByHousingDetail(Model model, HttpServletRequest request, HttpServletResponse response,
                                           @ModelAttribute TbUserMonthPerformance cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipDetail(cond,3);
            cond = commonTbUserMonthPerformanceApiService.dataAuth(MenuId.TCW_FinanceSalaryTotalByHousingDetail, cond);
            LayuiTableVoJA tableVoJA = commonTbUserMonthPerformanceApiService.searchLay(cond);

            //获取
            JSONArray tableVoJAData = tableVoJA.getData();

            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));

            //处理数据
            resList = setListByHousingDetail(resListNew);
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resList));
            tableVoJA.setData(jsonArray);

            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                //cond.setMainTable("TB_UserPaySlip");
                // 公积金-企业
                JO.put("accumulationFundCompany", cond.setFields("ISNULL(SUM(S.AccumulationFundCompany),0)").sumDecimal());
                // 公积金-个人
                JO.put("accumulationFundPersonal", cond.setFields("ISNULL(SUM(S.AccumulationFundPersonal),0)").sumDecimal());
                // 合计
                JO.put("accumulationFundTotal", cond.setFields("ISNULL(SUM(S.AccumulationFundCompany + S.AccumulationFundPersonal),0)").sumDecimal());

                tableVoJA.setTotalRow(JO);
            }

            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportByHousingDetail")
    @ResponseBody
    public R exportByHousingDetail(@ModelAttribute TbUserMonthPerformance cond,String conditions){
        try {
            String pointMonth = ConditionUtil.getConditionValue(conditions, "pointMonth");//统计月份
            if(pointMonth==null || "".equals(pointMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipDetail(cond,3);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('pointMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotalByHousingDetail)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();
            //处理数据
            listData = setListByHousingDetail(listData);

            //处理数据
            return exportByTotal(listField,"公积金扣款明细",listData,pointMonth,0);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }


    /**
     * 查询 --- 财务医保表-汇总
     */
    @RequiresPermissions("tcwFinanceSalaryTotalByHousingTotal:view")
    @RequestMapping("/searchByHousingTotal")
    @ResponseBody
    public LayuiTableVoJA searchByHousingTotal(Model model, HttpServletRequest request, HttpServletResponse response,
                                          @ModelAttribute TcwFinanceSalaryTotal cond,String conditions
    ){
        try {
            cond = setSeachSqlByPaySlipTotal(cond);
            cond.where("A.AccumulationFundTotal > 0 ");
            cond = commonApiService.dataAuth(MenuId.TCW_FinanceSalaryTotalByHousingTotal, cond);
            LayuiTableVoJA tableVoJA = commonApiService.searchLay(cond);
            //合计
            {
                JSONObject JO = new JSONObject();
                //计算累计
                cond.setMainTable("TCW_FinanceSalaryTotal");
                // 公积金-企业
                JO.put("accumulationFundCompany", cond.setFields("ISNULL(SUM(A.AccumulationFundCompany),0)").sumDecimal());
                // 公积金-个人
                JO.put("accumulationFundPersonal", cond.setFields("ISNULL(SUM(A.AccumulationFundPersonal),0)").sumDecimal());
                // 公积金合计
                JO.put("accumulationFundTotal", cond.setFields("ISNULL(SUM(A.AccumulationFundTotal),0)").sumDecimal());
                tableVoJA.setTotalRow(JO);
            }
            return tableVoJA;
        }catch (Exception e){
            logger.error("查询出错：",e.getMessage(),e);
            LayuiTableVoJA tableVoJA = new LayuiTableVoJA();
            tableVoJA.setCode(1);
            tableVoJA.setMsg("查询出错，请稍后重试！");
            return tableVoJA;
        }
    }

    /**
     * 导出
     */
    @RequestMapping("/exportByHousingTotal")
    @ResponseBody
    public R exportByHousingTotal(@ModelAttribute TcwFinanceSalaryTotal cond,String conditions){
        try {
            String countMonth = ConditionUtil.getConditionValue(conditions, "countMonth");//统计月份
            if(countMonth==null || "".equals(countMonth)){
                return R.error("请在搜索条件中选择要导出的月份！");
            }
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByPaySlipTotal(cond);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .where("A.Code NOT IN ('countMonth','orgName')")
                    .setMenuId(MenuId.TCW_FinanceSalaryTotalByHousingTotal)
                    .setFrontShow(1)
                    .setOrderby("A.SortNo asc")
                    .queryList();
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();

            //处理数据
            return exportByTotal(listField,"公积金小计",listData,countMonth,1);
        }catch (Exception e){
            logger.error("导出出错：",e.getMessage(),e);
            return R.error("导出失败，请稍后重试！");
        }
    }



    //设置表头信息
    public Map<String,List<String>> setHeadMsg(String titleMsg, List<YsCol> listField) {
        Map<String,List<String>> headMsg = new HashMap<>();
        //设置一级表头
        List<String> headerMsg1 = new ArrayList<>();
        headerMsg1.add(titleMsg);
        for(int i=1;i<listField.size();i++){
            headerMsg1.add("");
        }
        headMsg.put("headerMsg1",headerMsg1);
        //设置二级表头
        List<String> headerMsg2 = new ArrayList<>();
        for(int i=0;i<listField.size();i++){
            headerMsg2.add(listField.get(i).getName());
        }
        headMsg.put("headerMsg2",headerMsg2);
        return headMsg;
    }



    /**
     * 创建表格样式
     * @param wb 工作薄对象
     * @return 样式列表
     */
    public Map<String, CellStyle> createStyles(SXSSFWorkbook wb){
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);	//设置单元格的水平对齐类型（居左）
        style.setVerticalAlignment(VerticalAlignment.CENTER);	//设置单元格的垂直对齐类型（居中）
        style.setBorderRight(BorderStyle.THIN);	//设置单元格右边框的边框类型
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置右边边框的颜色
        style.setBorderLeft(BorderStyle.THIN);	//设置单元格左边框的边框类型
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置左边框的颜色
        style.setBorderTop(BorderStyle.THIN);	//设置单元格上边框使用的边框类型
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置上边框的颜色
        style.setBorderBottom(BorderStyle.THIN);	//设置单元格下边框使用的边框类型
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置下边框的颜色
        Font dataFont = wb.createFont();
        dataFont.setFontName("仿宋");	//设置字体的名称(例如:仿宋)
        dataFont.setFontHeightInPoints((short) 12);	//设置字体高度
        style.setFont(dataFont);
        styles.put("data", style);


        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);	//设置单元格的水平对齐类型（居左）
        style.setVerticalAlignment(VerticalAlignment.CENTER);	//设置单元格的垂直对齐类型（居中）
        Font dataFont2 = wb.createFont();
        dataFont2.setFontName("仿宋");	//设置字体的名称(例如:仿宋)
        dataFont2.setFontHeightInPoints((short) 12);	//设置字体高度
        //dataFont2.setBold(true);
        style.setFont(dataFont2);
        styles.put("otherdata", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);	//设置单元格的水平对齐类型（居中）
        style.setVerticalAlignment(VerticalAlignment.CENTER);	//设置单元格的垂直对齐类型（居中）
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());	//
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        dataFont.setFontName("仿宋");	//设置字体的名称(例如:仿宋)
        headerFont.setFontHeightInPoints((short) 16);	//设置字体高度
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);


        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);	//设置单元格的水平对齐类型（居中）
        style.setVerticalAlignment(VerticalAlignment.CENTER);	//设置单元格的垂直对齐类型（居中）
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());	//
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont2 = wb.createFont();
        dataFont.setFontName("仿宋");	//设置字体的名称(例如:仿宋)
        headerFont2.setFontHeightInPoints((short) 12);	//设置字体高度
        headerFont2.setBold(true);
        headerFont2.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont2);
        styles.put("header2", style);

        return styles;
    }



}